package demo.virtuemart.Util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;

//import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class xls_Reader {

	public  String path;
	public  FileInputStream fis = null;
	public  FileOutputStream fileOut =null;
	private XSSFWorkbook workbook = null;
	private XSSFSheet sheet = null;
	private XSSFRow row   =null;
	private XSSFCell cell = null;
	public int xlRowTS, xlColTS, xlRowTs, xlColTs;
	
	
public xls_Reader(String path) {
		
		this.path=path;
		try {
			fis = new FileInputStream(path);
			workbook = new XSSFWorkbook(fis);
			sheet = workbook.getSheetAt(0);
			fis.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		
	}
	// returns the row count in a sheet
		public int getRowCount(String sheetName) throws Throwable
		{
			File file = new File(path);
			fis = new FileInputStream(file);
			workbook = new XSSFWorkbook(fis);
			sheet = workbook.getSheet(sheetName);
			xlRowTS= sheet.getLastRowNum()+1;
			xlColTS = sheet.getRow(0).getLastCellNum();
			return xlRowTS;
		}
		
		public int getColCount(String sheetName) throws Throwable
		{
			File file = new File(path);
			fis = new FileInputStream(file);
			workbook = new XSSFWorkbook(fis);
			sheet = workbook.getSheet(sheetName);
			xlRowTS= sheet.getLastRowNum()+1;
			xlColTS = sheet.getRow(0).getLastCellNum();
			return xlColTS;
		}
		/*public String getCellData(String sheetName, int colNum,int rowNum) throws Throwable
		{
			try
			{
			File file = new File(path);
			fis = new FileInputStream(file);
			workbook = new XSSFWorkbook(fis);
			sheet = workbook.getSheet(sheetName);
			xlRowTS= sheet.getLastRowNum()+1;
			xlColTS = sheet.getRow(0).getLastCellNum();
			String[][] xlData = new String[xlRowTS][xlColTS];
			int col_Num=-1;
			for(int i=0; i<xlRowTS;i++)
			{
				row = sheet.getRow(i);
				for(int j=0;j<xlColTS;j++)
				{
					cell = row.getCell(j);
					if(row.getCell(i).getStringCellValue().trim().equals(colName.trim()))
						col_Num=i;
					String value = cell_to_String(cell);
					xlData[i][j] = value;
				}
			}
			if(col_Num==-1)
				return "";
			
			return xlData.toString();
			}
			catch(Exception e){
				
				e.printStackTrace();
				return "row "+rowNum+" or column "+colNum +" does not exist  in xls";
			}
		}
		*/
		public String getCellData(String sheetName, String colName,int rowNum) throws Throwable
		{
			try
			{
			File file = new File(path);
			fis = new FileInputStream(file);
			workbook = new XSSFWorkbook(fis);
			sheet = workbook.getSheet(sheetName);
			xlRowTS= sheet.getLastRowNum()+1;
			xlColTS = sheet.getRow(0).getLastCellNum();
			//int col_Num=-1;
			String[][] xlData = new String[xlRowTS][xlColTS];
			
			for(int i=0; i<xlRowTS;i++)
			{
				row = sheet.getRow(i);
				if(row==null)
					return "";
				for(int j=0;j<xlColTS;j++)
				{
					cell = row.getCell(j);
					
											
					if(cell.getStringCellValue().trim().equals(colName))
					{
						
						row = sheet.getRow(rowNum);
						cell= row.getCell(j);
						String value = cell_to_String(cell);
						xlData[i][j] = value;
						return xlData[i][j]; 
					}
					
					
				}
				
			}
			
			
			return xlData.toString();
			}
			catch(Exception e){
				
				e.printStackTrace();
				//return e.toString();
				return "row "+rowNum+" or column "+colName +" does not exist  in xls";
			}
		}
		
		public String getCellData(String sheetName, int colNum,int rowNum) throws Throwable
		{
			try
			{
			File file = new File(path);
			fis = new FileInputStream(file);
			workbook = new XSSFWorkbook(fis);
			sheet = workbook.getSheet(sheetName);
			xlRowTs= sheet.getLastRowNum()+1;
			xlColTs = sheet.getRow(0).getLastCellNum();
			String[][] xlData = new String[xlRowTS][xlColTS];
			
				row = sheet.getRow(rowNum);
				cell = row.getCell(colNum);
					String value = cell_to_String(cell);
					for(int i=0;i<xlRowTs;i++)
					{
						for(int j=0;j<xlColTs;j++)
						{
						xlData[i][j] = value;
						return xlData[i][j]; 
						}
					}
					return xlData.toString();
			}
			catch(Exception e){
				
				e.printStackTrace();
				//return e.toString();
				return "row "+rowNum+" or column "+colNum +" does not exist  in xls";
			}
		}
		
		public String cell_to_String(XSSFCell col){
			Object result;
			int type = col.getCellType();
			switch (type){
			case XSSFCell.CELL_TYPE_STRING:
				result = col.getStringCellValue();
				break;
			case XSSFCell.CELL_TYPE_NUMERIC:
				result = col.getNumericCellValue();
				break;
			case XSSFCell.CELL_TYPE_BLANK:
				result = "-";
				break;
			case XSSFCell.CELL_TYPE_BOOLEAN:
				result = col.getBooleanCellValue();
				break;
			case XSSFCell.CELL_TYPE_ERROR:
				throw new RuntimeException ("This cell has an error");
			case XSSFCell.CELL_TYPE_FORMULA:
				throw new RuntimeException("We can't evaluate formulas in Java");
			default:
				throw new RuntimeException("We don't support this cell type: " + type);
				
			}
			return result.toString();
		}
		
		public boolean isSheetExist(String sheetName){
			int index = workbook.getSheetIndex(sheetName);
			if(index==-1){
				index=workbook.getSheetIndex(sheetName.toUpperCase());
					if(index==-1)
						return false;
					else
						return true;
			}
			else
				return true;
		}
		
		// returns true if data is set successfully else false
		public boolean setCellData(String sheetName,String colName,int rowNum, String data){
			try{
			fis = new FileInputStream(path); 
			workbook = new XSSFWorkbook(fis);

			if(rowNum<=0)
				return false;
			
			int index = workbook.getSheetIndex(sheetName);
			int colNum=-1;
			if(index==-1)
				return false;
			
			
			sheet = workbook.getSheetAt(index);
			

			row=sheet.getRow(0);
			for(int i=0;i<row.getLastCellNum();i++){
				//System.out.println(row.getCell(i).getStringCellValue().trim());
				if(row.getCell(i).getStringCellValue().trim().equals(colName))
					colNum=i;
			}
			if(colNum==-1)
				return false;

			sheet.autoSizeColumn(colNum); 
			row = sheet.getRow(rowNum-1);
			if (row == null)
				row = sheet.createRow(rowNum-1);
			
			cell = row.getCell(colNum);	
			if (cell == null)
		        cell = row.createCell(colNum);

		    // cell style
		    //CellStyle cs = workbook.createCellStyle();
		    //cs.setWrapText(true);
		    //cell.setCellStyle(cs);
		    cell.setCellValue(data);

		    fileOut = new FileOutputStream(path);

			workbook.write(fileOut);

		    fileOut.close();	

			}
			catch(Exception e){
				e.printStackTrace();
				return false;
			}
			return true;
		}
		
}
